<!DOCTYPE html><!--[if IE 8]><html class="no-js lt-ie9" lang="en" > <![endif]--><!--[if gt IE 8]><!--><html class="no-js" lang="en"><!--<![endif]--><head>
  <meta charset="utf-8">
  <meta http-equiv="X-UA-Compatible" content="IE=edge">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  
  
  <link rel="canonical" href="https://sequelize.org/v3/docs/docs/querying/">
  <link rel="shortcut icon" href="/v3/favicon.ico">
  
  <title>Querying - Sequelize | The Node.js / io.js ORM for PostgreSQL, MySQL, SQLite and MSSQL</title>
  <link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Lato:400,700|Roboto+Slab:400,700|Inconsolata:400,700">

  <link rel="stylesheet" href="/v3/css/theme.css">
  <link rel="stylesheet" href="/v3/css/theme_extra.css">
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/highlight.js/10.5.0/styles/github.min.css">
  <link href="/v3/css/custom.css" rel="stylesheet">
  
  <script>
    // Current page data
    var mkdocs_page_name = "Querying";
    var mkdocs_page_input_path = "docs/querying.md";
    var mkdocs_page_url = "/v3/docs/querying/";
  </script>
  
  <script src="/v3/js/jquery-2.1.1.min.js" defer=""></script>
  <script src="/v3/js/modernizr-2.8.3.min.js" defer=""></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/highlight.js/10.5.0/highlight.min.js"></script>
  <script>hljs.initHighlightingOnLoad();</script> 
<meta name="robots" content="noindex"></head>

<body class="wy-body-for-nav" role="document">

  <div class="wy-grid-for-nav">

    
    <nav data-toggle="wy-nav-shift" class="wy-nav-side stickynav">
    <div class="wy-side-scroll">
      <div class="wy-side-nav-search">
        <a href="/v3" class="icon icon-home"> Sequelize | The Node.js / io.js ORM for PostgreSQL, MySQL, SQLite and MSSQL</a>
        <div role="search">
  <form id="rtd-search-form" class="wy-form" action="/v3/search.html" method="get">
      <input type="text" name="q" placeholder="Search docs" title="Type search term here">
  </form>
</div>
      </div>

      <div class="wy-menu wy-menu-vertical" data-spy="affix" role="navigation" aria-label="main navigation">
                <ul>
                    <li class="toctree-l1"><a class="reference internal" href="/v3">Home</a>
                    </li>
                </ul>
                <p class="caption"><span class="caption-text">Documentation</span></p>
                <ul class="current">
                    <li class="toctree-l1"><a class="reference internal" href="/v3/docs/getting-started/">Getting Started</a>
                    </li>
                    <li class="toctree-l1"><a class="reference internal" href="/v3/docs/schema/">Working with table schemas</a>
                    </li>
                    <li class="toctree-l1"><a class="reference internal" href="#">Models</a>
    <ul>
                <li class="toctree-l2"><a class="reference internal" href="/v3/docs/models-definition/">Definition</a>
                </li>
                <li class="toctree-l2"><a class="reference internal" href="/v3/docs/models-usage/">Usage</a>
                </li>
    </ul>
                    </li>
                    <li class="toctree-l1 current"><a class="reference internal current" href="./">Querying</a>
    <ul class="current">
    </ul>
                    </li>
                    <li class="toctree-l1"><a class="reference internal" href="/v3/docs/scopes/">Scopes</a>
                    </li>
                    <li class="toctree-l1"><a class="reference internal" href="/v3/docs/instances/">Instances</a>
                    </li>
                    <li class="toctree-l1"><a class="reference internal" href="/v3/docs/associations/">Relations / Associations</a>
                    </li>
                    <li class="toctree-l1"><a class="reference internal" href="/v3/docs/hooks/">Hooks</a>
                    </li>
                    <li class="toctree-l1"><a class="reference internal" href="/v3/docs/transactions/">Transactions</a>
                    </li>
                    <li class="toctree-l1"><a class="reference internal" href="/v3/docs/legacy/">Working with legacy tables</a>
                    </li>
                    <li class="toctree-l1"><a class="reference internal" href="/v3/docs/raw-queries/">Raw queries</a>
                    </li>
                    <li class="toctree-l1"><a class="reference internal" href="/v3/docs/migrations/">Migrations</a>
                    </li>
                </ul>
                <p class="caption"><span class="caption-text">API</span></p>
                <ul>
                    <li class="toctree-l1"><a class="reference internal" href="/v3/api/sequelize/">Sequelize</a>
                    </li>
                    <li class="toctree-l1"><a class="reference internal" href="/v3/api/model/">Model</a>
                    </li>
                    <li class="toctree-l1"><a class="reference internal" href="/v3/api/instance/">Instance</a>
                    </li>
                    <li class="toctree-l1"><a class="reference internal" href="#">Associations</a>
    <ul>
                <li class="toctree-l2"><a class="reference internal" href="/v3/api/associations/">Overview</a>
                </li>
                <li class="toctree-l2"><a class="reference internal" href="/v3/api/associations/belongs-to/">BelongsTo (1:1)</a>
                </li>
                <li class="toctree-l2"><a class="reference internal" href="/v3/api/associations/has-one/">HasOne (1:1)</a>
                </li>
                <li class="toctree-l2"><a class="reference internal" href="/v3/api/associations/has-many/">HasMany (1:m)</a>
                </li>
                <li class="toctree-l2"><a class="reference internal" href="/v3/api/associations/belongs-to-many/">BelongsToMany (n:m)</a>
                </li>
    </ul>
                    </li>
                    <li class="toctree-l1"><a class="reference internal" href="/v3/api/hooks/">Hooks</a>
                    </li>
                    <li class="toctree-l1"><a class="reference internal" href="/v3/api/transaction/">Transaction</a>
                    </li>
                    <li class="toctree-l1"><a class="reference internal" href="/v3/api/datatypes/">Datatypes</a>
                    </li>
                    <li class="toctree-l1"><a class="reference internal" href="/v3/api/deferrable/">Deferrable</a>
                    </li>
                    <li class="toctree-l1"><a class="reference internal" href="/v3/api/errors/">Errors</a>
                    </li>
                </ul>
                <p class="caption"><span class="caption-text">Misc</span></p>
                <ul>
                    <li class="toctree-l1"><a class="reference internal" href="/v3/changelog/">Changelog</a>
                    </li>
                    <li class="toctree-l1"><a class="reference internal" href="/v3/imprint/">Imprint</a>
                    </li>
                </ul>
      </div>
    </div>
    </nav>

    <section data-toggle="wy-nav-shift" class="wy-nav-content-wrap">

      
      <nav class="wy-nav-top" role="navigation" aria-label="top navigation">
        <i data-toggle="wy-nav-top" class="fa fa-bars"></i>
        <a href="/v3">Sequelize | The Node.js / io.js ORM for PostgreSQL, MySQL, SQLite and MSSQL</a>
      </nav>

      
      <div class="wy-nav-content">
        <div class="rst-content">
          <div role="navigation" aria-label="breadcrumbs navigation">
  <ul class="wy-breadcrumbs">
    <li><a href="/v3">Docs</a> »</li>
    
      
        
          <li>Documentation »</li>
        
      
    
    <li>Querying</li>
    <li class="wy-breadcrumbs-aside">
      
        <a href="https://github.com/sequelize/sequelize/edit/master/docs/docs/querying.md" class="icon icon-github"> Edit on GitHub</a>
      
    </li>
  </ul>
  
  <hr>
</div>

          <div role="main">
            <div class="section">
              
                <h2 id="attributes">Attributes</h2>
<p>To select only some attributes, you can use the <code>attributes</code> option. Most often, you pass an array:</p>
<pre><code class="language-js">Model.findAll({
  attributes: ['foo', 'bar']
});
</code></pre>
<pre><code class="language-sql">SELECT foo, bar ...
</code></pre>
<p>Attributes can be renamed using a nested array:</p>
<pre><code class="language-js">Model.findAll({
  attributes: ['foo', ['bar', 'baz']]
});
</code></pre>
<pre><code class="language-sql">SELECT foo, bar AS baz ...
</code></pre>
<p>You can use <code>sequelize.fn</code> to do aggregations:</p>
<pre><code class="language-js">Model.findAll({
  attributes: [[sequelize.fn('COUNT', sequelize.col('hats')), 'no_hats']]
});
</code></pre>
<pre><code class="language-sql">SELECT COUNT(hats) AS no_hats ...
</code></pre>
<p>When using aggregation function, you must give it an alias to be able to access it from the model. In the example above you can get the number of hats with <code>instance.get('no_hats')</code>.</p>
<p>Sometimes it may be tiresome to list all the attributes of the model if you only want to add an aggregation:</p>
<pre><code class="language-js">// This is a tiresome way of getting the number of hats...
Model.findAll({
  attributes: ['id', 'foo', 'bar', 'baz', 'quz', [sequelize.fn('COUNT', sequelize.col('hats')), 'no_hats']]
});

// This is shorter, and less error prone because it still works if you add / remove attributes
Model.findAll({
  attributes: { include: [[sequelize.fn('COUNT', sequelize.col('hats')), 'no_hats']] }
});
</code></pre>
<pre><code class="language-sql">SELECT id, foo, bar, baz, quz, COUNT(hats) AS no_hats ...
</code></pre>
<p>Similarly, its also possible to remove a selected few attributes:</p>
<pre><code class="language-js">Model.findAll({
  attributes: { exclude: ['baz'] }
});
</code></pre>
<pre><code class="language-sql">SELECT id, foo, bar, quz ...
</code></pre>
<h2 id="where">Where</h2>
<p>Whether you are querying with findAll/find or doing bulk updates/destroys you can pass a <code>where</code> object to filter the query.</p>
<p><code>where</code> generally takes an object from attribute:value pairs, where value can be primitives for equality matches or keyed objects for other operators.</p>
<p>It's also possible to generate complex AND/OR conditions by nesting sets of <code>$or</code> and <code>$and</code>.</p>
<h3 id="basics">Basics</h3>
<pre><code class="language-js">Post.findAll({
  where: {
    authorId: 2
  }
});
// SELECT * FROM post WHERE authorId = 2

Post.findAll({
  where: {
    authorId: 12,
    status: 'active'
  }
});
// SELECT * FROM post WHERE authorId = 12 AND status = 'active';

Post.destroy({
  where: {
    status: 'inactive'
  }
});
// DELETE FROM post WHERE status = 'inactive';

Post.update({
  updatedAt: null,
}, {
  where: {
    deletedAt: {
      $ne: null
    }
  }
});
// UPDATE post SET updatedAt = null WHERE deletedAt NOT NULL;

Post.findAll({
  where: sequelize.where(sequelize.fn('char_length', sequelize.col('status')), 6)
});
// SELECT * FROM post WHERE char_length(status) = 6;
</code></pre>
<h3 id="operators">Operators</h3>
<pre><code class="language-js">$and: {a: 5}           // AND (a = 5)
$or: [{a: 5}, {a: 6}]  // (a = 5 OR a = 6)
$gt: 6,                // &gt; 6
$gte: 6,               // &gt;= 6
$lt: 10,               // &lt; 10
$lte: 10,              // &lt;= 10
$ne: 20,               // != 20
$not: true,            // IS NOT TRUE
$between: [6, 10],     // BETWEEN 6 AND 10
$notBetween: [11, 15], // NOT BETWEEN 11 AND 15
$in: [1, 2],           // IN [1, 2]
$notIn: [1, 2],        // NOT IN [1, 2]
$like: '%hat',         // LIKE '%hat'
$notLike: '%hat'       // NOT LIKE '%hat'
$iLike: '%hat'         // ILIKE '%hat' (case insensitive) (PG only)
$notILike: '%hat'      // NOT ILIKE '%hat'  (PG only)
$like: { $any: ['cat', 'hat']}
                       // LIKE ANY ARRAY['cat', 'hat'] - also works for iLike and notLike
$overlap: [1, 2]       // &amp;&amp; [1, 2] (PG array overlap operator)
$contains: [1, 2]      // @&gt; [1, 2] (PG array contains operator)
$contained: [1, 2]     // &lt;@ [1, 2] (PG array contained by operator)
$any: [2,3]            // ANY ARRAY[2, 3]::INTEGER (PG only)

$col: 'user.organization_id' // = "user"."organization_id", with dialect specific column identifiers, PG in this example
</code></pre>
<h3 id="combinations">Combinations</h3>
<pre><code class="language-js">{
  rank: {
    $or: {
      $lt: 1000,
      $eq: null
    }
  }
}
// rank &lt; 1000 OR rank IS NULL

{
  createdAt: {
    $lt: new Date(),
    $gt: new Date(new Date() - 24 * 60 * 60 * 1000)
  }
}
// createdAt &lt; [timestamp] AND createdAt &gt; [timestamp]

{
  $or: [
    {
      title: {
        $like: 'Boat%'
      }
    },
    {
      description: {
        $like: '%boat%'
      }
    }
  ]
}
// title LIKE 'Boat%' OR description LIKE '%boat%'
</code></pre>
<h3 id="jsonb">JSONB</h3>
<p>JSONB can be queried in three different ways.</p>
<h4 id="nested-object">Nested object</h4>
<pre><code class="language-js">{
  meta: {
    video: {
      url: {
        $ne: null
      }
    }
  }
}
</code></pre>
<h4 id="nested-key">Nested key</h4>
<pre><code class="language-js">{
  "meta.audio.length": {
    $gt: 20
  }
}
</code></pre>
<h4 id="containment">Containment</h4>
<pre><code class="language-js">{
  "meta": {
    $contains: {
      site: {
        url: 'http://google.com'
      }
    }
  }
}
</code></pre>
<h3 id="relations-associations">Relations / Associations</h3>
<pre><code class="language-js">// Find all projects with a least one task where task.state === project.task
Project.findAll({
    include: [{
        model: Task,
        where: { state: Sequelize.col('project.state') }
    }]
})
</code></pre>
<h2 id="pagination-limiting">Pagination / Limiting</h2>
<pre><code class="language-js">// Fetch 10 instances/rows
Project.findAll({ limit: 10 })

// Skip 8 instances/rows
Project.findAll({ offset: 8 })

// Skip 5 instances and fetch the 5 after that
Project.findAll({ offset: 5, limit: 5 })
</code></pre>
<h2 id="ordering">Ordering</h2>
<p><code>order</code> takes an array of items to order the query by. Generally you will want to use a tuple/array of either attribute, direction or just direction to ensure proper escaping.</p>
<pre><code class="language-js">something.findOne({
  order: [
    // Will escape username and validate DESC against a list of valid direction parameters
    ['username', 'DESC'],

    // Will order by max(age)
    sequelize.fn('max', sequelize.col('age')),

    // Will order by max(age) DESC
    [sequelize.fn('max', sequelize.col('age')), 'DESC'],

    // Will order by  otherfunction(`col1`, 12, 'lalala') DESC
    [sequelize.fn('otherfunction', sequelize.col('col1'), 12, 'lalala'), 'DESC'],

    // Will order by name on an associated User
    [User, 'name', 'DESC'],

    // Will order by name on an associated User aliased as Friend
    [{model: User, as: 'Friend'}, 'name', 'DESC'],

    // Will order by name on a nested associated Company of an associated User
    [User, Company, 'name', 'DESC'],
  ]
  // All the following statements will be treated literally so should be treated with care
  order: 'convert(user_name using gbk)'
  order: 'username DESC'
  order: sequelize.literal('convert(user_name using gbk)')
})
</code></pre>
              
            </div>
          </div>
          <footer>
  
    <div class="rst-footer-buttons" role="navigation" aria-label="footer navigation">
      
        <a href="/v3/docs/scopes/" class="btn btn-neutral float-right" title="Scopes">Next <span class="icon icon-circle-arrow-right"></span></a>
      
      
        <a href="/v3/docs/models-usage/" class="btn btn-neutral" title="Usage"><span class="icon icon-circle-arrow-left"></span> Previous</a>
      
    </div>
  

  <hr>

  <div role="contentinfo">
    <!-- Copyright etc -->
    
  </div>

  Built with <a href="https://www.mkdocs.org/">MkDocs</a> using a <a href="https://github.com/snide/sphinx_rtd_theme">theme</a> provided by <a href="https://readthedocs.org">Read the Docs</a>.
</footer>
      
        </div>
      </div>

    </section>

  </div>

  <div class="rst-versions" role="note" aria-label="versions">
  <span class="rst-current-version" data-toggle="rst-current-version">
    
        <span>
          <a href="https://github.com/sequelize/sequelize/" class="fa fa-github" style="color: #fcfcfc"> GitHub</a>
        </span>
    
    
      <span><a href="/v3/docs/models-usage/" style="color: #fcfcfc">« Previous</a></span>
    
    
      <span><a href="/v3/docs/scopes/" style="color: #fcfcfc">Next »</a></span>
    
  </span>
</div>
    <script>var base_url = '../..';</script>
    <script src="/v3/js/theme_extra.js" defer=""></script>
    <script src="/v3/js/theme.js" defer=""></script>
      <script src="//cdnjs.cloudflare.com/ajax/libs/highlight.js/8.4/highlight.min.js" defer=""></script>
      <script src="/v3/search/main.js" defer=""></script>
    <script defer="">
        window.onload = function () {
            SphinxRtdTheme.Navigation.enable(true);
        };
    </script>



</body></html>